package ${projectDomain}.pojo.dao.sql;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.unswift.annotation.api.ApiConstructor;
import com.unswift.annotation.api.ApiEntity;
import com.unswift.annotation.api.ApiField;
import com.unswift.annotation.api.ApiMethod;
import com.unswift.utils.DateUtils;
import com.unswift.utils.ObjectUtils;

@SuppressWarnings("serial")
@ApiEntity(value="自定义sql对象", author="unswift", date="2023-07-18", version="1.0.0")
public class Sql implements SqlKeyword{
	
	@ApiMethod(value="创建默认sql对象", returns=@ApiField("sql对象"))
	public static Sql createSql(){
		return new Sql();
	}
	
	@ApiMethod(value="创建带分页信息的sql对象", params={@ApiField("开始记录数"), @ApiField("每页大小")}, returns=@ApiField("sql对象"))
	public static Sql createSql(int firstSize, int pageSize){
		return new Sql(firstSize, pageSize);
	}
	
	@ApiField("是否查询基础字段")
	private Boolean baseSelect;
	
	@ApiField("查询的字段集合")
	private List<SqlSelect> selectList;
	
	@ApiField("是否查询基础表")
	private Boolean baseFrom;
	
	@ApiField("查询的表集合")
	private List<SqlFrom> fromList;
	
	@ApiField("更新的字段集合")
	private List<SqlSet> setList;
	
	@ApiField("是否表字段的基础查询")
	private Boolean baseWhere;
	
	@ApiField("查询的条件集合")
	private List<SqlWhere> whereList;
	
	@ApiField("分组条件")
	private List<String> groupByList;
	
	@ApiField("排序字段")
	private List<SqlOrderBy> orderByList;
	
	@ApiField("查询开始记录数")
	protected Integer firstSize;
	
	@ApiField("查询条数")
	protected Integer pageSize;
	
	@ApiConstructor("默认构造函数")
	public Sql() {
		
	}
	
	@ApiConstructor(value="带分页信息的构造函数", params={@ApiField("开始记录数"), @ApiField("每页大小")})
	public Sql(Integer firstSize, Integer pageSize) {
		super();
		this.firstSize = firstSize;
		this.pageSize = pageSize;
	}
	
	@ApiMethod(value="获取sql的select对象列表，表示需要查询的字段")
	public List<SqlSelect> getSelectList() {
		return selectList;
	}
	
	@ApiMethod(value="根据字段名增加一个查询结果项", params=@ApiField("查询的字段"))
	public void addSelect(String field) {
		this.addSelect(field, null);
	}
	
	@ApiMethod(value="根据字段名和别名增加一个查询结果项", params={@ApiField("查询的字段名"), @ApiField("别名")})
	public void addSelect(String field, String alias) {
		if(ObjectUtils.isNull(this.selectList)){
			this.selectList=new ArrayList<SqlSelect>();
		}
		this.selectList.add(new SqlSelect(field, alias));
	}
	
	@ApiMethod(value="获取sql的from对象列表", returns=@ApiField("from对象列表"))
	public List<SqlFrom> getFromList() {
		return fromList;
	}
	
	@ApiMethod(value="根据表名称增加一个form对象", params=@ApiField("表名"))
	public void addFrom(String table) {
		this.addFrom(table, null);
	}
	
	@ApiMethod(value="根据表名称和别名增加一个form对象", params={@ApiField("表名"), @ApiField("别名")})
	public void addFrom(String table, String alias) {
		this.addFrom(table, alias, null, null);
	}
	
	@ApiMethod(value="根据表信息及关联关系增加一个form对象", params={@ApiField("表名"), @ApiField("别名"), @ApiField("关联关系join,left join等，可查阅SqlKeyword"), @ApiField("关联的条件")})
	public void addFrom(String table, String alias, String joinOnType, String joinOnCondition) {
		if(ObjectUtils.isNull(this.fromList)){
			this.fromList=new ArrayList<SqlFrom>();
		}
		this.fromList.add(new SqlFrom(table, alias, joinOnType, joinOnCondition));
	}
	
	@ApiMethod(value="根据表信息及关联条件增加一个join(form)对象，关联关系为join", params={@ApiField("表名"), @ApiField("别名"), @ApiField("关联的条件")})
	public void addJoin(String table, String alias, String joinOnCondition) {
		this.addFrom(table, alias, FROM_JOIN, joinOnCondition);
	}
	
	@ApiMethod(value="根据表信息及关联条件增加一个left join(form)对象，关联关系为left join", params={@ApiField("表名"), @ApiField("别名"), @ApiField("关联的条件")})
	public void addLeftJoin(String table, String alias, String joinOnCondition) {
		this.addFrom(table, alias, FROM_LEFT_JOIN, joinOnCondition);
	}
	
	@ApiMethod(value="增加更新字段", params = {@ApiField("更新的字段"), @ApiField("更新的字段值")})
	public void addSet(String field, String value) {
		if(ObjectUtils.isNull(this.setList)){
			this.setList=new ArrayList<SqlSet>();
		}
		this.setList.add(new SqlSet(field, value));
	}
	
	@ApiMethod(value="获取sql更新字段集合", returns=@ApiField("sql更新字段集合"))
	public List<SqlSet> getSetList() {
		return setList;
	}
	
	@ApiMethod(value="获取sql的where对象列表", returns=@ApiField("where对象列表"))
	public List<SqlWhere> getWhereList() {
		return whereList;
	}
	
	@ApiMethod(value="根据where对象增加一个where对象", params=@ApiField("where对象，使用规范可查阅SqlWhere类"))
	public void addWhere(SqlWhere where) {
		if(ObjectUtils.isNull(this.whereList)){
			this.whereList=new ArrayList<SqlWhere>();
		}
		this.whereList.add(where);
	}
	
	@ApiMethod(value="根据条件信息增加一个and(where)对象，最终结果如：and a=b", params={@ApiField("查询的字段"), @ApiField("比较运算符，参阅：SqlKeyword类"), @ApiField("查询的值数组，当compare=between时可设置2个值，当compare=in可设置多个值")})
	public void addWhereAnd(String field, String compare, Object...valueArray) {
		addWhere(new SqlWhere(LOGIC_AND, field, compare, valueArray));
	}
	
	@ApiMethod(value="根据条件信息增加一个or(where)对象，最终结果如：or a=b", params={@ApiField("查询的字段"), @ApiField("比较运算符，参阅：SqlKeyword类"), @ApiField("查询的值数组，当compare=between时可设置2个值，当compare=in可设置多个值")})
	public void addWhereOr(String field, String compare, Object...valueArray) {
		addWhere(new SqlWhere(LOGIC_OR, field, compare, valueArray));
	}
	
	@ApiMethod(value="根据条件信息增加一个where对象，最终结果如：and|or a=b", 
			params={@ApiField("逻辑运算符，参阅：SqlKeyword类"), 
					@ApiField("查询的字段"), 
					@ApiField("比较运算符，参阅：SqlKeyword类"), 
					@ApiField("查询的值数组，当compare=between时可设置2个值，当compare=in可设置多个值")})
	public void addWhere(String logic, String field, String compare, Object...valueArray) {
		addWhere(new SqlWhere(logic, field, compare, valueArray));
	}
	
	@ApiMethod(value="根据条件信息增加一个and(where)对象，最终结果如：and (a=b...)", 
			params={@ApiField("代码块开始，为true时相等于("),
					@ApiField("代码块结束，为true时相等于)"),
					@ApiField("查询的字段"), 
					@ApiField("比较运算符，参阅：SqlKeyword类"), 
					@ApiField("查询的值数组，当compare=between时可设置2个值，当compare=in可设置多个值")})
	public void addWhereAnd(boolean start, boolean end, String field, String compare, Object...valueArray) {
		addWhere(new SqlWhere(LOGIC_AND, start, end, field, compare, valueArray));
	}
	
	@ApiMethod(value="根据条件信息增加一个and(where)对象，最终结果如：or (a=b...)", 
			params={@ApiField("代码块开始，为true时相等于("),
					@ApiField("代码块结束，为true时相等于)"),
					@ApiField("查询的字段"), 
					@ApiField("比较运算符，参阅：SqlKeyword类"), 
					@ApiField("查询的值数组，当compare=between时可设置2个值，当compare=in可设置多个值")})
	public void addWhereOr(boolean start, boolean end, String field, String compare, Object...valueArray) {
		addWhere(new SqlWhere(LOGIC_OR, start, end, field, compare, valueArray));
	}
	
	@ApiMethod(value="根据条件信息增加一个and(where)对象，最终结果如：and (a=b...)", 
			params={@ApiField("逻辑运算符，参阅：SqlKeyword类"), 
					@ApiField("代码块开始，为true时相等于("),
					@ApiField("代码块结束，为true时相等于)"),
					@ApiField("查询的字段"), 
					@ApiField("比较运算符，参阅：SqlKeyword类"), 
					@ApiField("查询的值数组，当compare=between时可设置2个值，当compare=in可设置多个值")})
	public void addWhere(String logic, boolean start, boolean end, String field, String compare, Object...valueArray) {
		addWhere(new SqlWhere(logic, start, end, field, compare, valueArray));
	}
	
	@ApiMethod(value="根据存在语句增加一个exists(where)对象，最终结果如：and|or exists(...)", params={@ApiField("逻辑运算符，参阅：SqlKeyword类"), @ApiField("存在的子语句")})
	public void addExists(String logic, String exists){
		addWhere(new SqlWhere(logic, null, COMPARE_EXISTS, exists));
	}
	
	@ApiMethod(value="根据存在语句增加一个exists(where)对象，最终结果如：and exists(...)", params={@ApiField("存在的子语句")})
	public void addExistsAnd(String exists){
		addWhere(new SqlWhere(LOGIC_AND, null, COMPARE_EXISTS, exists));
	}
	
	@ApiMethod(value="根据存在语句增加一个exists(where)对象，最终结果如：or exists(...)", params={@ApiField("存在的子语句")})
	public void addExistsOr(String exists){
		addWhere(new SqlWhere(LOGIC_AND, null, COMPARE_EXISTS, exists));
	}
	
	@ApiMethod(value="根据存在语句增加一个exists(where)对象，最终结果如：and|or exists(...)", 
			params={@ApiField("逻辑运算符，参阅：SqlKeyword类"), 
					@ApiField("代码块开始，为true时相等于("),
					@ApiField("代码块结束，为true时相等于)"),		
					@ApiField("存在的子语句")})
	public void addExists(String logic, boolean start, boolean end, String exists){
		addWhere(new SqlWhere(logic, start, end, null, COMPARE_EXISTS, exists));
	}
	
	@ApiMethod(value="根据存在语句增加一个exists(where)对象，最终结果如：and exists(...)", 
			params={@ApiField("代码块开始，为true时相等于("),
					@ApiField("代码块结束，为true时相等于)"),		
					@ApiField("存在的子语句")})
	public void addExistsAnd(boolean start, boolean end, String exists){
		addWhere(new SqlWhere(LOGIC_AND, start, end, null, COMPARE_EXISTS, exists));
	}
	
	@ApiMethod(value="根据存在语句增加一个exists(where)对象，最终结果如：or exists(...)",
			params={@ApiField("代码块开始，为true时相等于("),
					@ApiField("代码块结束，为true时相等于)"),		
					@ApiField("存在的子语句")})
	public void addExistsOr(boolean start, boolean end, String exists){
		addWhere(new SqlWhere(LOGIC_AND, start, end, null, COMPARE_EXISTS, exists));
	}
	
	@ApiMethod(value="获取sql的group by列表", returns=@ApiField("分组列表"))
	public List<String> getGroupByList() {
		return groupByList;
	}
	
	@ApiMethod(value="根据分组字段增加分组", params=@ApiField("分组的字段"))
	public void addGroupBy(String groupBy){
		if(ObjectUtils.isNull(this.groupByList)){
			this.groupByList=new ArrayList<String>();
		}
		this.groupByList.add(groupBy);
	}
	
	@ApiMethod(value="获取sql的order by对象")
	public List<SqlOrderBy> getOrderByList() {
		return orderByList;
	}
	
	@ApiMethod(value="根据字段及升降序创建排序对象", params={@ApiField("排序字段"), @ApiField("升序或降序，参考：SqlKeyword类")})
	public void addOrderBy(String field, String ascDesc){
		addOrderBy(new SqlOrderBy(field, ascDesc));
	}
	
	@ApiMethod(value="根据排序对象增加排序", params=@ApiField("排序对象"))
	public void addOrderBy(SqlOrderBy orderBy){
		if(ObjectUtils.isNull(this.orderByList)){
			this.orderByList=new ArrayList<SqlOrderBy>();
		}
		this.orderByList.add(orderBy);
	}
	
	@ApiMethod(value="获取查询的开始记录数", returns=@ApiField("开始记录数"))
	public Integer getFirstSize() {
		return firstSize;
	}
	
	@ApiMethod(value="设置查询的开始记录数", params=@ApiField("开始记录数"))
	public void setFirstSize(Integer firstSize) {
		this.firstSize = firstSize;
	}
	
	@ApiMethod(value="获取查询的页大小", returns=@ApiField("页大小"))
	public Integer getPageSize() {
		return pageSize;
	}
	
	@ApiMethod(value="设置查询的页大小", params=@ApiField("页大小"))
	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}
	
	@ApiMethod(value="获取是否查询基础表的字段", returns=@ApiField("是否查询基础表的字段"))
	public Boolean getBaseSelect() {
		return ObjectUtils.isEmpty(baseSelect)?true:baseSelect;
	}
	
	@ApiMethod(value="设置是否查询基础表的字段", params=@ApiField("是否查询基础表的字段"))
	public void setBaseSelect(Boolean baseSelect) {
		this.baseSelect = baseSelect;
	}
	
	@ApiMethod(value="获取是否查询基础表", returns=@ApiField("是否查询基础表"))
	public Boolean getBaseFrom() {
		return baseFrom;
	}
	
	@ApiMethod(value="设置是否查询基础表", params=@ApiField("是否查询基础表"))
	public void setBaseFrom(Boolean baseFrom) {
		this.baseFrom = baseFrom;
	}
	
	@ApiMethod(value="获取是否使用基础表查询条件", returns=@ApiField("是否使用基础表查询条件"))
	public Boolean getBaseWhere() {
		return baseWhere;
	}
	
	@ApiMethod(value="设置是否使用基础表查询条件", params=@ApiField("是否使用基础表查询条件"))
	public void setBaseWhere(Boolean baseWhere) {
		this.baseWhere = baseWhere;
	}
	
	@ApiMethod(value="处理sql值的方法，应用于防sql注入", params=@ApiField("传入的值"), returns=@ApiField("处理后的值"))
	public static String sqlValue(Object source){
		return sqlValue(source, null);
	}
	
	@ApiMethod(value="处理sql值的方法，应用于防sql注入", params={@ApiField("传入的值"), @ApiField("值的格式")}, returns=@ApiField("处理后的值"))
	public static String sqlValue(Object source, String format){
		if(ObjectUtils.isNull(source)){
			return null;
		}
		if(source instanceof Number){
			return source.toString();
		}else if(source instanceof CharSequence){
			return "'"+source.toString().replace("'", "\\'")+"'";
		}else if(source instanceof Date){
			format=ObjectUtils.isEmpty(format)?"yyyy-MM-dd":format;
			return "'"+DateUtils.format((Date)source, format)+"'";
		}else{
			return "'"+source+"'";
		}
	}
	
	@ApiMethod(value="修饰sql关键词，如：a会变为`a`,a.b会变为a.`b`", params=@ApiField("需要修饰的词"), returns=@ApiField("修饰后的词"))
	public static String keywordSql(String keyword){
		int index=keyword.lastIndexOf(".");
		if(index!=-1){
			index++;
			return String.format("%s%s%s%s", keyword.substring(0, index), KEYWORD_DECORATE, keyword.substring(index), KEYWORD_DECORATE);
		}
		return String.format("%s%s%s", KEYWORD_DECORATE, keyword.substring(index), KEYWORD_DECORATE);
	}
	
	public static String format(String sql, Object...args) {
		if(sql.indexOf("%")!=-1) {
			sql=sql.replace("%", "%%");
		}
		sql=sql.replace("?","%s");
		return String.format(sql, args);
	}
	
	@Override
	@ApiMethod(value="将当前sql对象转换为sql语句", returns=@ApiField("sql语句"))
	public String toSql(){
		StringBuilder sql=new StringBuilder();
		if(ObjectUtils.isNotEmpty(selectList)){
			sql.append(SELECT);
			for (SqlSelect select : selectList) {
				sql.append(select.toSql()).append(COMMA);
			}
			sql=sql.deleteCharAt(sql.length()-1);
		}
		if(ObjectUtils.isNotEmpty(setList)) {
			sql.append(UPDATE);
		}else {
			sql.append(FROM);
		}
		if(ObjectUtils.isNotEmpty(fromList)){
			for (SqlFrom from : fromList) {
				sql.append(from.toSql()).append(SPACE);
			}
			sql=sql.deleteCharAt(sql.length()-1);
		}
		if(ObjectUtils.isNotEmpty(setList)) {
			sql.append(SET);
			for (SqlSet sqlSet : setList) {
				sql.append(sqlSet.toSql()).append(COMMA);
			}
			sql.deleteCharAt(sql.length()-1);
			sql.append(SPACE);
		}
		if(ObjectUtils.isNotEmpty(whereList)){
			sql.append(WHERE);
			int i=0;
			for (SqlWhere where : whereList) {
				String whereSql = where.toSql();
				sql.append(i==0?whereSql.substring(whereSql.indexOf(SPACE)+1):whereSql).append(SPACE);
				i++;
			}
			sql=sql.deleteCharAt(sql.length()-1);
		}
		if(ObjectUtils.isNotEmpty(orderByList)){
			sql.append(ORDER_BY);
			for (SqlOrderBy orderBy : orderByList) {
				sql.append(orderBy.toSql()).append(COMMA);
			}
			sql=sql.deleteCharAt(sql.length()-1);
		}
		if(ObjectUtils.isNotEmpty(groupByList)){
			sql.append(GROUP_BY);
			for (String groupBy : groupByList) {
				sql.append(groupBy).append(COMMA);
			}
			sql=sql.deleteCharAt(sql.length()-1);
		}
		if(ObjectUtils.isNotEmpty(firstSize) && ObjectUtils.isNotEmpty(pageSize)) {
			sql.append(LIMIT).append(firstSize).append(COMMA).append(pageSize);
		}
		return sql.toString();
	}
}
